using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;


using Material.Object;
using Material.DAL;
namespace Material.Entity
{
    public class TblBarangEntity
    {
        private Accessor accessor;
        private string connString;

        public TblBarangEntity(string _connString)
        {
            this.connString = _connString;
        }

        public int Insert(TblBarang tblbarang)
        {
            StringBuilder sql = new StringBuilder();
            sql.AppendLine("INSERT INTO [TblBarang]");
            sql.AppendLine("(");
            sql.AppendLine("[NamaBarang]");
            sql.AppendLine(",[IdKelompok]");
            sql.AppendLine(",[IdSatuan]");
            sql.AppendLine(",[Jumlah]");
            sql.AppendLine(",[Harga3]");
            sql.AppendLine(",[Harga2]");
            sql.AppendLine(",[Harga1]");
            sql.AppendLine(",[TglBeli]");
            sql.AppendLine(",[TotalHarga]");
            sql.AppendLine(",[CreatedDate]");
            sql.AppendLine(",[CreatedBy]");
            sql.AppendLine(")");
            sql.AppendLine(" VALUES");
            sql.AppendLine("(");
            sql.AppendLine("@NamaBarang");
            sql.AppendLine(",@IdKelompok");
            sql.AppendLine(",@IdSatuan");
            sql.AppendLine(",@Jumlah");
            sql.AppendLine(",@Harga3");
            sql.AppendLine(",@Harga2");
            sql.AppendLine(",@Harga1");
            sql.AppendLine(",@TglBeli");
            sql.AppendLine(",@TotalHarga");
            sql.AppendLine(",@CreatedDate");
            sql.AppendLine(",@CreatedBy");
            sql.AppendLine(")");

            int result = -1;
            accessor = new Accessor(true, connString);
            accessor.CommandText = sql.ToString();
            accessor.CommandType = CommandType.Text;
            accessor.Add("@NamaBarang", tblbarang.NamaBarang);
            accessor.Add("@IdKelompok", tblbarang.IdKelompok);
            accessor.Add("@IdSatuan", tblbarang.IdSatuan);
            accessor.Add("@CreatedDate", tblbarang.CreatedDate);
            accessor.Add("@CreatedBy", tblbarang.CreatedBy);
            accessor.Add("@Jumlah", tblbarang.Jumlah);
            accessor.Add("@Harga1", tblbarang.Harga1);
            accessor.Add("@Harga2", tblbarang.Harga2);
            accessor.Add("@Harga3", tblbarang.Harga3);
            accessor.Add("@TglBeli", tblbarang.TglBeli);
            accessor.Add("@TotalHarga", tblbarang.TotalHarga);

            result = Excutor.ExecuteNonQuery(accessor);
            if (result != -1)
                accessor.CommitTransaction();
            else
                accessor.RollBackTransaction();
            return result;
        }

        public int UpdateJmlBarang(TblBarang tblbarang)
        {
            StringBuilder sql = new StringBuilder();
            sql.AppendLine("UPDATE [TblBarang]");
            sql.AppendLine("SET [Jumlah] =@Jumlah ");
            sql.AppendLine(" ,[ModifiedDate] = @ModifiedDate");
            sql.AppendLine(",[ModifiedBy] = @ModifiedBy");
            sql.AppendLine("WHERE Id=@Id");



            int result = -1;
            accessor = new Accessor(true, connString);
            accessor.CommandText = sql.ToString();
            accessor.CommandType = CommandType.Text;
            accessor.Add(@"Id", tblbarang.Id);
            accessor.Add(@"Jumlah", tblbarang.Jumlah);
            accessor.Add(@"ModifiedDate", tblbarang.ModifiedDate);
            accessor.Add(@"ModifiedBy", tblbarang.ModifiedBy);

            result = Excutor.ExecuteNonQuery(accessor);
            if (result != -1)
                accessor.CommitTransaction();
            else
                accessor.RollBackTransaction();
            return result;
        }
        public int Update(TblBarang tblbarang)
        {
            StringBuilder sql = new StringBuilder();
            sql.AppendLine("UPDATE [TblBarang]");
            sql.AppendLine("SET [NamaBarang] =@NamaBarang ");
            sql.AppendLine(",[IdKelompok] = @IdKelompok");
            sql.AppendLine(",[IdSatuan] =@IdSatuan ");
            sql.AppendLine(" ,[Jumlah] = @Jumlah");
            sql.AppendLine(",[Harga3] = @Harga3");
            sql.AppendLine(",[Harga2] = @Harga2");
            sql.AppendLine(",[Harga1] = @Harga1 ");
            sql.AppendLine(",[TglBeli] = @TglBeli");
            sql.AppendLine(",[TotalHarga] = @TotalHarga ");
            sql.AppendLine(" ,[ModifiedDate] = @ModifiedDate");
            sql.AppendLine(",[ModifiedBy] = @ModifiedBy");
            sql.AppendLine("WHERE Id=@Id");



            int result = -1;
            accessor = new Accessor(true, connString);
            accessor.CommandText = sql.ToString();
            accessor.CommandType = CommandType.Text;
            accessor.Add(@"Id", tblbarang.Id);
            accessor.Add(@"NamaBarang", tblbarang.NamaBarang);
            accessor.Add(@"IdSatuan", tblbarang.IdSatuan);
            accessor.Add(@"IdKelompok", tblbarang.IdSatuan);
            accessor.Add(@"Jumlah", tblbarang.Jumlah);
            accessor.Add(@"Harga3", tblbarang.Harga3);
            accessor.Add(@"Harga2", tblbarang.Harga2);
            accessor.Add(@"Harga1", tblbarang.Harga1);
            accessor.Add(@"TglBeli", tblbarang.TglBeli);
            accessor.Add(@"TotalHarga", tblbarang.TotalHarga);
            accessor.Add(@"ModifiedDate", tblbarang.ModifiedDate);
            accessor.Add(@"ModifiedBy", tblbarang.ModifiedBy);

           

            result = Excutor.ExecuteNonQuery(accessor);
            if (result != -1)
                accessor.CommitTransaction();
            else
                accessor.RollBackTransaction();
            return result;
        }

        public int Delete(TblBarang tblbarang)
        {

            StringBuilder sql = new StringBuilder();
            sql.AppendLine(" DELETE FROM [TblBarang]");
            sql.AppendLine("  WHERE Id=@Id" );


            int result = -1;
            accessor = new Accessor(true, connString);
            accessor.CommandText = sql.ToString();
            accessor.CommandType = CommandType.Text;
            accessor.Add(@"Id", tblbarang.Id);

            result = Excutor.ExecuteNonQuery(accessor);
            if (result != -1)
                accessor.CommitTransaction();
            else
                accessor.RollBackTransaction();
            return result;
        }

        public TblBarang GetDataByID(TblBarang tblbarang)
        {
            StringBuilder sql = new StringBuilder();
            sql.AppendLine("SELECT [Id]");
            sql.AppendLine(",[NamaBarang]");
            sql.AppendLine(",[IdKelompok]");
            sql.AppendLine(",[IdSatuan]");
            sql.AppendLine(",[Jumlah]");
            sql.AppendLine(",[Harga3]");
            sql.AppendLine(",[Harga2]");
            sql.AppendLine(",[Harga1]");
            sql.AppendLine(",[TglBeli]");
            sql.AppendLine(",[TotalHarga]");
            sql.AppendLine(",[CreatedDate]");
            sql.AppendLine(",[CreatedBy]");
            sql.AppendLine(",[ModifiedDate]");
            sql.AppendLine(",[ModifiedBy]");
            sql.AppendLine("FROM [TblBarang]");
            sql.AppendLine("WHERE Id=@Id");



            TblBarang result = new TblBarang();
            TblKelompok tblKelompok;// = new TblKelompok();
            TblKelompokEntity tblKelompokEntity;

            TblSatuan tblSatuan;
            TblSatuanEntity tblSatuanEntity;

            accessor = new Accessor(true, connString);
            accessor.CommandText = sql.ToString();
            accessor.CommandType = CommandType.Text;
            accessor.Add(@"Id", tblbarang.Id);

            SqlDataReader rdr = Excutor.GetDataReader(accessor);
            if (rdr.HasRows)
            {
                while (rdr.Read())
                {
                    result.Id = rdr.GetInt32(0);
                    result.NamaBarang = rdr.GetString(1);
                    result.IdKelompok = rdr.GetInt32(2);
                    result.IdSatuan = rdr.GetInt32(3);
                   
                    if(rdr[4].ToString()!="")
                        result.Jumlah = rdr.GetInt32(4);
                    if (rdr[5].ToString() != "")
                        result.Harga1 = rdr.GetDecimal(5);
                    if (rdr[6].ToString() != "")
                        result.Harga2 = rdr.GetDecimal(6);
                    if (rdr[7].ToString() != "")
                        result.Harga3 = rdr.GetDecimal(7);
                    if (rdr[8].ToString() != "")
                        result.TglBeli = rdr.GetDateTime(8);
                    if (rdr[9].ToString() != "")
                        result.TotalHarga = rdr.GetDecimal(9);
                    result.CreatedDate = rdr.GetDateTime(10);
                    result.CreatedBy = rdr.GetString(11);
                    if(rdr[12]!=DBNull.Value)
                    result.ModifiedDate = rdr.GetDateTime(12);
                    if (rdr[13] != DBNull.Value)
                    result.ModifiedBy = rdr.GetString(13);

                tblKelompokEntity = new TblKelompokEntity(connString);
                tblKelompok = new TblKelompok();
                tblKelompok.Id = result.IdKelompok;
                tblKelompok = tblKelompokEntity.GetDataByID(tblKelompok);
                result.TblKelompok = tblKelompok;

                tblSatuanEntity = new TblSatuanEntity(connString);
                tblSatuan = new TblSatuan();
                tblSatuan.Id = Convert.ToInt32(result.IdSatuan);
                tblSatuan = tblSatuanEntity.GetDataByID(tblSatuan);
                result.TblSatuan = tblSatuan;
                    
                }
            }
            return result;
        }

      

      
        public List<TblBarang> GetDataAll()
        {

            StringBuilder sql = new StringBuilder();
            sql.AppendLine("SELECT [Id]");
            sql.AppendLine(",[NamaBarang]");
            sql.AppendLine(",[IdKelompok]");
            sql.AppendLine(",[IdSatuan]");
            sql.AppendLine(",[Jumlah]");
            sql.AppendLine(",[Harga3]");
            sql.AppendLine(",[Harga2]");
            sql.AppendLine(",[Harga1]");
            sql.AppendLine(",[TglBeli]");
            sql.AppendLine(",[TotalHarga]");
            sql.AppendLine(",[CreatedDate]");
            sql.AppendLine(",[CreatedBy]");
            sql.AppendLine(",[ModifiedDate]");
            sql.AppendLine(",[ModifiedBy]");
            sql.AppendLine("FROM [TblBarang]");
            sql.AppendLine("ORDER BY NamaBarang ASC");


            List<TblBarang> result = new List<TblBarang>();
            TblKelompok tblKelompok;// = new TblKelompok();
            TblKelompokEntity tblKelompokEntity;

            TblSatuan tblSatuan;
            TblSatuanEntity tblSatuanEntity;

            accessor = new Accessor(true, connString);
            accessor.CommandText = sql.ToString();
            accessor.CommandType = CommandType.Text;

            SqlDataReader rdr = Excutor.GetDataReader(accessor);
            if (rdr.HasRows)
            {
                while (rdr.Read())
                {
                    TblBarang obj = new TblBarang();
                    obj.Id = rdr.GetInt32(0);
                    obj.NamaBarang = rdr.GetString(1);
                    obj.IdKelompok = rdr.GetInt32(2);
                    obj.IdSatuan = rdr.GetInt32(3);
                    obj.Keterangan = rdr.GetString(4);
                    obj.CreatedDate = rdr.GetDateTime(5);
                    obj.CreatedBy = rdr.GetString(6);
                    obj.ModifiedDate = rdr.GetDateTime(7);
                    obj.ModifiedBy = rdr.GetString(8);

                    tblKelompokEntity = new TblKelompokEntity(connString);
                    tblKelompok = new TblKelompok();
                    tblKelompok.Id = obj.IdKelompok;
                    tblKelompok = tblKelompokEntity.GetDataByID(tblKelompok);
                    obj.TblKelompok = tblKelompok;

                    tblSatuanEntity = new TblSatuanEntity(connString);
                    tblSatuan = new TblSatuan();
                    tblSatuan.Id = Convert.ToInt32(obj.IdSatuan);
                    tblSatuan = tblSatuanEntity.GetDataByID(tblSatuan);
                    obj.TblSatuan = tblSatuan;

                    result.Add(obj);
                }
            }
            return result;
        }

        public List<TblBarang> GetDataByIdKelompok(TblBarang tblBarang)
        {

            StringBuilder sql = new StringBuilder();
            sql.AppendLine("SELECT [Id]");
            sql.AppendLine(",[NamaBarang]");
            sql.AppendLine(",[IdKelompok]");
            sql.AppendLine(",[IdSatuan]");
            sql.AppendLine(",[Jumlah]");
            sql.AppendLine(",[Harga3]");
            sql.AppendLine(",[Harga2]");
            sql.AppendLine(",[Harga1]");
            sql.AppendLine(",[TglBeli]");
            sql.AppendLine(",[TotalHarga]");
            sql.AppendLine(",[CreatedDate]");
            sql.AppendLine(",[CreatedBy]");
            sql.AppendLine(",[ModifiedDate]");
            sql.AppendLine(",[ModifiedBy]");
            sql.AppendLine("FROM [TblBarang]");
            sql.AppendLine("WHERE IdKelompok = @IdKelompok");
            sql.AppendLine("ORDER BY NamaBarang ASC");


            List<TblBarang> result = new List<TblBarang>();
            TblKelompok tblKelompok;// = new TblKelompok();
            TblKelompokEntity tblKelompokEntity;

            TblSatuan tblSatuan;
            TblSatuanEntity tblSatuanEntity;

            accessor = new Accessor(true, connString);
            accessor.CommandText = sql.ToString();
            accessor.CommandType = CommandType.Text;
            accessor.Add(@"IdKelompok", tblBarang.IdKelompok);


            SqlDataReader rdr = Excutor.GetDataReader(accessor);
            if (rdr.HasRows)
            {
                while (rdr.Read())
                {
                    TblBarang obj = new TblBarang();
                    obj.Id = rdr.GetInt32(0);
                    obj.NamaBarang = rdr.GetString(1);
                    obj.IdKelompok = rdr.GetInt32(2);
                    obj.IdSatuan = rdr.GetInt32(3);
                    obj.Keterangan = rdr.GetString(4);
                    obj.CreatedDate = rdr.GetDateTime(5);
                    obj.CreatedBy = rdr.GetString(6);
                    obj.ModifiedDate = rdr.GetDateTime(7);
                    obj.ModifiedBy = rdr.GetString(8);

                    tblKelompokEntity = new TblKelompokEntity(connString);
                    tblKelompok = new TblKelompok();
                    tblKelompok.Id = obj.IdKelompok;
                    tblKelompok = tblKelompokEntity.GetDataByID(tblKelompok);
                    obj.TblKelompok = tblKelompok;

                    tblSatuanEntity = new TblSatuanEntity(connString);
                    tblSatuan = new TblSatuan();
                    tblSatuan.Id = Convert.ToInt32(obj.IdSatuan);
                    tblSatuan = tblSatuanEntity.GetDataByID(tblSatuan);
                    obj.TblSatuan = tblSatuan;

                    result.Add(obj);
                }
            }
            return result;
        }


        public List<TblBarang> GetDataByIdSatuan(TblBarang tblBarang)
        {
            StringBuilder sql = new StringBuilder();
            sql.AppendLine("SELECT [Id]");
            sql.AppendLine(",[NamaBarang]");
            sql.AppendLine(",[IdKelompok]");
            sql.AppendLine(",[IdSatuan]");
            sql.AppendLine(",[Jumlah]");
            sql.AppendLine(",[Harga3]");
            sql.AppendLine(",[Harga2]");
            sql.AppendLine(",[Harga1]");
            sql.AppendLine(",[TglBeli]");
            sql.AppendLine(",[TotalHarga]");
            sql.AppendLine(",[CreatedDate]");
            sql.AppendLine(",[CreatedBy]");
            sql.AppendLine(",[ModifiedDate]");
            sql.AppendLine(",[ModifiedBy]");
            sql.AppendLine("FROM [TblBarang]");
            sql.AppendLine("WHERE IdSatuan = @IdSatuan");
            sql.AppendLine("ORDER BY NamaBarang ASC");


            List<TblBarang> result = new List<TblBarang>();
            TblKelompok tblKelompok;// = new TblKelompok();
            TblKelompokEntity tblKelompokEntity;

            TblSatuan tblSatuan;
            TblSatuanEntity tblSatuanEntity;

            accessor = new Accessor(true, connString);
            accessor.CommandText = sql.ToString();
            accessor.CommandType = CommandType.Text;
            accessor.Add(@"IdSatuan", tblBarang.IdSatuan);


            SqlDataReader rdr = Excutor.GetDataReader(accessor);
            if (rdr.HasRows)
            {
                while (rdr.Read())
                {
                    TblBarang obj = new TblBarang();
                    obj.Id = rdr.GetInt32(0);
                    obj.NamaBarang = rdr.GetString(1);
                    obj.IdKelompok = rdr.GetInt32(2);
                    obj.IdSatuan = rdr.GetInt32(3);
                    obj.Keterangan = rdr.GetString(4);
                    obj.CreatedDate = rdr.GetDateTime(5);
                    obj.CreatedBy = rdr.GetString(6);
                    obj.ModifiedDate = rdr.GetDateTime(7);
                    obj.ModifiedBy = rdr.GetString(8);

                    tblKelompokEntity = new TblKelompokEntity(connString);
                    tblKelompok = new TblKelompok();
                    tblKelompok.Id = obj.IdKelompok;
                    tblKelompok = tblKelompokEntity.GetDataByID(tblKelompok);
                    obj.TblKelompok = tblKelompok;

                    tblSatuanEntity = new TblSatuanEntity(connString);
                    tblSatuan = new TblSatuan();
                    tblSatuan.Id = Convert.ToInt32(obj.IdSatuan);
                    tblSatuan = tblSatuanEntity.GetDataByID(tblSatuan);
                    obj.TblSatuan = tblSatuan;

                    result.Add(obj);
                }
            }
            return result;
        }

        public List<TblBarang> GetDataByNamaBarang(TblBarang tblBarang)
        {
            StringBuilder sql = new StringBuilder();
            sql.AppendLine("SELECT [Id]");
            sql.AppendLine(",[NamaBarang]");
            sql.AppendLine(",[IdKelompok]");
            sql.AppendLine(",[IdSatuan]");
            sql.AppendLine(",[Jumlah]");
            sql.AppendLine(",[Harga3]");
            sql.AppendLine(",[Harga2]");
            sql.AppendLine(",[Harga1]");
            sql.AppendLine(",[TglBeli]");
            sql.AppendLine(",[TotalHarga]");
            sql.AppendLine(",[CreatedDate]");
            sql.AppendLine(",[CreatedBy]");
            sql.AppendLine(",[ModifiedDate]");
            sql.AppendLine(",[ModifiedBy]");
            sql.AppendLine("FROM [TblBarang]");
            sql.AppendLine("WHERE NamaBarang LIKE "+  "'%" + tblBarang.NamaBarang + "%'");
            sql.AppendLine("ORDER BY NamaBarang ASC");


            List<TblBarang> result = new List<TblBarang>();
            TblKelompok tblKelompok;// = new TblKelompok();
            TblKelompokEntity tblKelompokEntity;

            TblSatuan tblSatuan;
            TblSatuanEntity tblSatuanEntity;

            accessor = new Accessor(true, connString);
            accessor.CommandText = sql.ToString();
            accessor.CommandType = CommandType.Text;
           


            SqlDataReader rdr = Excutor.GetDataReader(accessor);
            if (rdr.HasRows)
            {
                while (rdr.Read())
                {
                    TblBarang obj = new TblBarang();
                    obj.Id = rdr.GetInt32(0);
                    obj.NamaBarang = rdr.GetString(1);
                    obj.IdKelompok = rdr.GetInt32(2);
                    obj.IdSatuan = rdr.GetInt32(3);
                    obj.Keterangan = rdr.GetString(4);
                    obj.CreatedDate = rdr.GetDateTime(5);
                    obj.CreatedBy = rdr.GetString(6);
                    obj.ModifiedDate = rdr.GetDateTime(7);
                    obj.ModifiedBy = rdr.GetString(8);

                    tblKelompokEntity = new TblKelompokEntity(connString);
                    tblKelompok = new TblKelompok();
                    tblKelompok.Id = obj.IdKelompok;
                    tblKelompok = tblKelompokEntity.GetDataByID(tblKelompok);
                    obj.TblKelompok = tblKelompok;

                    tblSatuanEntity = new TblSatuanEntity(connString);
                    tblSatuan = new TblSatuan();
                    tblSatuan.Id = Convert.ToInt32(obj.IdSatuan);
                    tblSatuan = tblSatuanEntity.GetDataByID(tblSatuan);
                    obj.TblSatuan = tblSatuan;

                    result.Add(obj);
                }
            }
            return result;
        }

        public SqlCommand GetCommandReport()
        {
            StringBuilder sql = new StringBuilder();

            sql.AppendLine("Select 		A.Id	AS	'Id',");
            sql.AppendLine("A.NamaBarang	AS	'NamaBarang',");
            sql.AppendLine("A.IdKelompok	AS	'IdKelompok',");
            sql.AppendLine("A.IdSatuan	AS	'IdSatuan',");
            sql.AppendLine("A.Jumlah AS 'Jumlah',");
            sql.AppendLine("A.Harga3 AS 'Harga3',");
            sql.AppendLine("A.Harga2 AS 'Harga2',");
            sql.AppendLine("A.Harga1 AS 'Harga1',");
            sql.AppendLine("A.TglBeli AS 'TglBeli',");
            sql.AppendLine("A.TotalHarga AS 'TotalHarga',");
            sql.AppendLine("A.CreatedDate	AS	'CreatedDate',");
            sql.AppendLine("A.CreatedBy	AS	'CreatedBy',");
            sql.AppendLine("A.ModifiedDate	AS	'ModifiedDate',");
            sql.AppendLine("A.ModifiedBy	AS	'ModifiedBy'");
            sql.AppendLine(",B.Satuan");
            sql.AppendLine(",C.Kelompok");
            sql.AppendLine("From	TblBarang AS A,");
            sql.AppendLine("TblSatuan AS B,");
            sql.AppendLine("TblKelompok AS C");
            sql.AppendLine("WHERE A.IdSatuan = B.Id ");
            sql.AppendLine("and  A.IdKelompok = C.Id ORDER BY A.NamaBarang ASC");



            SqlCommand comm = new SqlCommand();

            accessor = new Accessor(true, connString);
            accessor.CommandText = sql.ToString();
            accessor.CommandType = CommandType.Text;
            comm = accessor.Command;

            return comm;
        }


        public SqlCommand GetCommandReportByDate(DateTime TglAwal,DateTime TglAkhir)
        {

            //SqlCommand comm = new SqlCommand();

            //accessor = new Accessor(true, connString);
            //accessor.CommandText = "TblBarang_SelectReportByDate";
            //accessor.CommandType = CommandType.StoredProcedure;
            //accessor.Add("@TglAwal", TglAwal);
            //accessor.Add("@TglAkhir", TglAkhir);
            //comm = accessor.Command;

            //return comm;

            StringBuilder sql = new StringBuilder();

            sql.AppendLine("Select 		A.Id	AS	'Id',");
            sql.AppendLine("A.NamaBarang	AS	'NamaBarang',");
            sql.AppendLine("A.IdKelompok	AS	'IdKelompok',");
            sql.AppendLine("A.IdSatuan	AS	'IdSatuan',");
            sql.AppendLine("A.Jumlah AS 'Jumlah',");
            sql.AppendLine("A.Harga3 AS 'Harga3',");
            sql.AppendLine("A.Harga2 AS 'Harga2',");
            sql.AppendLine("A.Harga1 AS 'Harga1',");
            sql.AppendLine("A.TglBeli AS 'TglBeli',");
            sql.AppendLine("A.TotalHarga AS 'TotalHarga',");
            sql.AppendLine("A.CreatedDate	AS	'CreatedDate',");
            sql.AppendLine("A.CreatedBy	AS	'CreatedBy',");
            sql.AppendLine("A.ModifiedDate	AS	'ModifiedDate',");
            sql.AppendLine("A.ModifiedBy	AS	'ModifiedBy'");
            sql.AppendLine(",B.Satuan");
            sql.AppendLine(",C.Kelompok");
            sql.AppendLine("From	TblBarang AS A,");
            sql.AppendLine("TblSatuan AS B,");
            sql.AppendLine("TblKelompok AS C");
            sql.AppendLine("WHERE A.IdSatuan = B.Id ");
            sql.AppendLine("and  A.IdKelompok = C.Id ");
             sql.AppendLine("AND A.CreatedDate >=  CONVERT(DATETIME, '"+TglAwal.ToString("yyyy-MM-dd")+"', 111)");
             sql.AppendLine("AND A.CreatedDate <=  CONVERT(DATETIME, '" + TglAkhir.ToString("yyyy-MM-dd") + "', 111)");
             sql.AppendLine("ORDER BY A.NamaBarang ASC");


            SqlCommand comm = new SqlCommand();

            accessor = new Accessor(true, connString);
            accessor.CommandText = sql.ToString();
            accessor.CommandType = CommandType.Text;
            comm = accessor.Command;

            return comm;
        }

        public DataSet GetDataAllKelompokSatuan()
        {

            StringBuilder sql = new StringBuilder();
            sql.AppendLine("Select 		A.Id	AS	'Id',");
            sql.AppendLine("A.NamaBarang	AS	'NamaBarang',");
            sql.AppendLine("A.IdKelompok	AS	'IdKelompok',");
            sql.AppendLine("A.IdSatuan	AS	'IdSatuan',");
            sql.AppendLine("A.Jumlah AS 'Jumlah',");
            sql.AppendLine("A.Harga1 AS 'Harga1',");
            sql.AppendLine("A.Harga2 AS 'Harga2',");
            sql.AppendLine("A.Harga2 AS 'Harga3',");
            sql.AppendLine("A.TglBeli AS 'TglBeli',");
            sql.AppendLine("A.TotalHarga AS 'TotalHarga',");
            sql.AppendLine("A.CreatedDate	AS	'CreatedDate',");
            sql.AppendLine("A.CreatedBy	AS	'CreatedBy',");
            sql.AppendLine("A.ModifiedDate	AS	'ModifiedDate',");
            sql.AppendLine("A.ModifiedBy	AS	'ModifiedBy',");
            sql.AppendLine("B.Satuan,");
            sql.AppendLine("C.Kelompok");
            sql.AppendLine("From	TblBarang AS A INNER JOIN TblSatuan AS B");
            sql.AppendLine("ON A.IdSatuan = B.Id INNER JOIN TblKelompok AS C");
            sql.AppendLine("ON A.IdKelompok = C.Id");
            sql.AppendLine("ORDER BY A.NamaBarang ASC");


            DataSet result = new DataSet();

            accessor = new Accessor(true, connString);
            accessor.CommandText = sql.ToString();
            accessor.CommandType = CommandType.Text;

           result = Excutor.GetDataSet(accessor);

           return result;
        }

        public DataSet GetFindDataAll()
        {
            StringBuilder s = new StringBuilder();
            s.AppendLine("SELECT");
            s.AppendLine(" C.NamaBarang");
            s.AppendLine(",C.Id AS  IdBarang");
            s.AppendLine(",D.Satuan");
            s.AppendLine(",E.Kelompok");
            s.AppendLine(",C.Harga1");
            s.AppendLine(",C.Harga2");
            s.AppendLine(",C.Harga3");
            s.AppendLine(",C.JUMLAH");
            s.AppendLine("FROM TblBarang as C INNER JOIN");
            s.AppendLine("TblSatuan AS D ON D.Id = C.IdSatuan INNER JOIN TblKelompok AS E");
            s.AppendLine("ON E.Id = C.IdKelompok");
            s.AppendLine("WHERE C.JUMLAH<>0");
            s.AppendLine("ORDER BY C.NamaBarang ASC");


            DataSet result = new DataSet();
            accessor = new Accessor(true, connString);
            accessor.CommandText = s.ToString();// "TblTransPemBarangDTL_SelectFindAll";
            accessor.CommandType = CommandType.Text;

            result = Excutor.GetDataSet(accessor);

            return result;
        }

        public DataSet GetFindDataAll(string parameter, string value)
        {
            if (parameter.Trim().Contains("NamaBarang"))
                parameter = "WHERE C.NamaBarang LIKE '%" + value + "%'";

            else if (parameter.Trim().Contains("KelompokBarang"))
                parameter = "WHERE E.Kelompok LIKE '%" + value + "%'";

            else if (parameter.Trim().Contains("SatuanBarang"))
                parameter = "WHERE D.SATUAN LIKE '%" + value + "%'";


            StringBuilder s = new StringBuilder();
            s.AppendLine("SELECT");
            s.AppendLine(" C.NamaBarang");
            s.AppendLine(",C.Id AS IdBarang");
            s.AppendLine(",D.Satuan");
            s.AppendLine(",E.Kelompok");
            s.AppendLine("FROM TblBarang AS C INNER JOIN");
            s.AppendLine("TblSatuan AS D ON D.Id = C.IdSatuan INNER JOIN TblKelompok AS E");
            s.AppendLine("ON E.Id = C.IdKelompok");
            s.AppendLine(parameter);
            s.AppendLine("ORDER BY C.NamaBarang ASC");

            DataSet result = new DataSet();
            accessor = new Accessor(true, connString);
            accessor.CommandText = s.ToString();
            accessor.CommandType = CommandType.Text;
            result = Excutor.GetDataSet(accessor);

            return result;
        }

        public DataSet GetDataPencarianPembelian(string tglAwal, string tglAkhir)
        {
            StringBuilder s = new StringBuilder();
            s.AppendLine("SELECT");
            s.AppendLine("A.NamaBarang");
            s.AppendLine(",'(' + CAST(A.Jumlah AS Varchar) +')'+' ' + B.Satuan AS Jumlah");
            s.AppendLine(",A.Harga1");
            s.AppendLine(",A.Harga2");
            s.AppendLine(",A.Harga3");
            s.AppendLine("FROM TblBarang A");
            s.AppendLine("INNER JOIN TblSatuan B ON A.IdSatuan = B.Id");
            s.AppendLine("WHERE A.Jumlah > 0");
            s.AppendLine("AND convert(varchar,A.TglBeli, 103) BETWEEN '"+tglAwal+"' AND '"+tglAkhir+"'");
            DataSet result = new DataSet();
            accessor = new Accessor(true, connString);
            accessor.CommandText = s.ToString();
            accessor.CommandType = CommandType.Text;


            result = Excutor.GetDataSet(accessor);

            return result;
        }



    }
}
